package com.deng.common.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.common.dao.ICommonPage;
import com.deng.common.vo.CommonPageVo;

public class CommonPageImpl extends SQLHelper implements ICommonPage {
//输入 where   , nowPage,
	
	
	private final static int _ROWS = 80;
//	public static void main(String s[]){
//		CommonPageVo vo = new CommonPageVo();
//		vo.searchColumn = null;
//		vo.nowPage = 110;
//		vo.tableName = "t_org_organization_item_count_month";
//		vo.where = "stat_time = '2010-5-1' and org_operate_organization_id in ('14I1','31I2')";
//		vo.specialOrder = "stat_time";
//		new CommonPageImpl().getData(vo);
//	}
//	public Connection getConnect() throws Exception{
//		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//		Connection con=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=PZSYYDB","performance","performance");
//		return con;
//	}
//	protected ResultSet queryData(Connection conn,String sql){
//		System.out.println("::::queryData:::::   "+sql);
//		PreparedStatement pstmt = null;
//		try{
//			pstmt = conn.prepareStatement(sql);
//			ResultSet rs = pstmt.executeQuery();
//			return rs;
//		}catch(Exception ex){
//			ex.printStackTrace();	
//		}
//		return null;
//	}
	public CommonPageVo getData(CommonPageVo vo) {
		try{
			Connection conn = getConnect();
			String tableName = vo.tableName;
			int totalRows = vo.totalRows;
			int totalPages = vo.totalPages;
			int nowPage = vo.nowPage;
			String where = vo.where;
			String specialOrder = vo.specialOrder;
			String searchColumn = vo.searchColumn;
			if(searchColumn == null || "*".equals(searchColumn.trim())){
				searchColumn = " * ";
			}
			if(vo.specialOrder == null || "".equals(specialOrder.trim())){
				vo.specialOrder = "id";
			}
			if(vo.pageRows == 0){
				vo.pageRows = _ROWS;
			}
			
			StringBuffer searchData = new StringBuffer();
			if(totalPages == 0){
				String searchPage = "SELECT COUNT(1) FROM "+tableName+" WHERE "+where;
				ResultSet rs = queryData(conn, searchPage);
				if(rs.next()){
					vo.totalRows = rs.getInt(1);//总行数
					if(vo.totalRows%vo.pageRows > 0){
						vo.totalPages = vo.totalRows/vo.pageRows+1;//总页数
					}else{
						vo.totalPages = vo.totalRows/vo.pageRows;//总页数
					}	
				}
			}
			
			
			if(nowPage == 0 || nowPage == 1){//说明没有查询过，需要重新查询
				if(vo.where == null || "".equals(vo.where.trim())){
					searchData.append("SELECT "+changeToReturnColumn(searchColumn)+" FROM" +
							"	(" +
							"select row_number() over(order by "+vo.specialOrder+") as row_number," +
							searchColumn+" from " +tableName+
							") as a" +
							"	WHERE row_number <= "+vo.pageRows);
				}else{
					searchData.append("SELECT "+changeToReturnColumn(searchColumn)+" FROM" +
							"	(" +
							"select row_number() over(order by "+vo.specialOrder+") as row_number," +
							searchColumn+" from " +tableName+
							" WHERE "+vo.where+") as a" +
							"	WHERE  row_number <= "+vo.pageRows);
				}
				
			}else{//中间查找，直接找页面
				if(vo.where == null || "".equals(vo.where.trim())){
					searchData.append("SELECT "+changeToReturnColumn(searchColumn)+" FROM" +
							"	(" +
							"select row_number() over(order by "+vo.specialOrder+") as row_number," +
							searchColumn+" from " +tableName+
							") as a" +
							"	WHERE row_number <= "+(vo.pageRows*vo.nowPage)+" and row_number > "+(vo.pageRows*(vo.nowPage-1)));
				}else{
					searchData.append("SELECT "+changeToReturnColumn(searchColumn)+" FROM" +
							"	(" +
							"select row_number() over(order by "+vo.specialOrder+") as row_number," +
							searchColumn+" from " +tableName+
							" WHERE "+vo.where+") as a" +
							"	WHERE row_number <= "+(vo.pageRows*vo.nowPage)+" and row_number > "+(vo.pageRows*(vo.nowPage-1)));
				}
			}
			ResultSet rs = queryData(conn, searchData.toString());
			
			List<String> list ;
			int columnCount = 0;
			vo.datas = new ArrayList();
			while(rs.next()){
				list = new ArrayList<String>();
				if(columnCount == 0){
					java.sql.ResultSetMetaData   rsmt   =   rs.getMetaData();
					columnCount = rsmt.getColumnCount();
				}
				
				for(int i = 0;i < columnCount; i ++){
					list.add(convertDataToString(rs.getObject(i+1)));
				}
				vo.datas.add(list);
			}
			return vo;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	private String changeToReturnColumn(String str){
		String sa[] = str.split(",");
		StringBuffer sb = new StringBuffer();
		for(int i = 0;i<sa.length;i++){
			System.out.println("....."+sa[i]);
			if(sa[i].indexOf(" as ") > -1){
				sb.append(sa[i].split(" as ")[1].trim()+",");
			}else{
				sb.append(sa[i]+",");
			}
			
		}
		String rtStr = sb.toString().substring(0, sb.length()-1);
		System.out.println("查询："+rtStr);
		return rtStr;
	}
	
}
